var mysql = require('mysql');
var mssql = require('mssql')
var fs = require('fs')

class TDBMs {
    constructor(){
        this.apps= {
            rtcChannel: {
                dbInfo: {
                    server: 'video.xmgkfw.cn',
                    user: 'sa',
                    password: 'ABCabc123',
                    //database    : 'rtcChannel',
                    database: 'rtcChannel',
                    port: 8201,
                    options: {
                        encrypt: true //使用windows azure，需要设置次配置。
                    },
                    pool: {
                        min: 0,
                        idleTimeoutMillis: 3000
                    }
                },
                sqlPath: "D:/gitee/hhudev/svr/datas/sqls"
            },
            directing: {
                dbInfo: {
                    server: 'video.xmgkfw.cn',
                    user: 'sa',
                    password: 'ABCabc123',
                    //database    : 'rtcChannel',
                    database: 'rtcChannel',
                    port: 8201,
                    options: {
                        encrypt: true //使用windows azure，需要设置次配置。
                    },
                    pool: {
                        min: 0,
                        idleTimeoutMillis: 3000
                    }
                },
                sqlPath: "D:/gitee/hhudev/svr/datas/sqls"
            }
        };
        this.pool = {};
    };
    query( appId ,  sql , ps , callback){
        var me = this;
        var queryRes ={
            appId: appId,
            result:null
        };
        try{
            if( !me.apps[appId]){
                queryRes.error="appId is error.";
                callback(queryRes);
                return;
            }

            if( me.pool[appId]==null){
                me.pool[appId] = new mssql.ConnectionPool(me.apps[appId].dbInfo);
            }
            me.pool[appId].connect(function (errConn , connection) {
                if( errConn ){
                    queryRes.error = errConn;
                    callback (queryRes);
                }
                else{
                    var dbReq = connection.request();
                    if( ps && ps!=null){
                        var getType = function (arg) {
                            var dataType= mssql.NVarChar;
                            if( arg && typeof(arg) == "string"){
                                dataType= mssql.NVarChar;
                            }
                            else if( arg && typeof(arg) == "number" && !isNaN(arg)){
                                if( Number.isInteger(arg)){
                                    dataType= mssql.Int;
                                }
                                else{
                                    dataType = mssql.Float;
                                }
                            }
                            return dataType;
                        }
                        for ( var p in ps){
                            var v = ps[p];
                            var vType = getType(v);
                            dbReq.input(p,vType,v);
                        }
                    }
                    dbReq.query(sql , function (erQuery, resQuery) {
                        try{
                            queryRes.result = resQuery;
                            callback (queryRes);
                        }
                        catch (eer) { 
                            console.log(eer);
                        }
                    })
                    connection.release();
                }
            })
        }
        catch (e) {
            console.log(e)
        }
    };
    queryByFn( appId ,  sqlPath , ps , callback){
        var me = this;
        var queryRes ={
            result:null
        };
        try{
            if( !me.apps[appId]){
                queryRes.error="appId is error.";
                callback(queryRes);
                return;
            }

            var fn = [me.apps[appId].sqlPath , sqlPath].join('/');
            fn = fn.replace("\\",'/').replace('//','/');
            fn = fn + (fn.indexOf('.txt')>0?"":".txt");
            if( fs.existsSync(fn)){
                var sql = fs.readFileSync(fn, 'utf-8');
                me.query(appId , sql , ps , callback);
            }
            else{
                queryRes.error="query Key is error.["+sqlFn+"]";
                callback(queryRes);
            }
        }
        catch (e) {
            console.log(e)
        }
    };
    setConfig(dbKey , dbInfo){
        var me = this;
        try{
            me.apps[dbKey]= dbInfo;
        }
        catch(er){
            console.log(er);
        }
    };
    regist2WebSvr(webSvr){
        var me = this;
        if( webSvr && webSvr!=null){
            var respJson = function(result , response){
                try{
                    response.send(JSON.stringify(result));
                }
                catch(eer){
                    console.log(eer);
                }
            }
            webSvr.registRoute ( "common" , "msdb" , "query" , function( result , request , response ){                
                try{
                    var appId = result.ps.appId ; 
                    var key = result.ps.key ; 
                    var psJson = result.ps.psJson;
                    var ps = {};
                    try{
                        ps = JSON.parse(psJson);
                    }
                    catch(eer){
                        ps={};
                    }
                    me.queryByFn(appId , key , ps ).then((res)=>{
                        result.data = res;
                        respJson(result, response);
                    });
                }
                catch(err){
                    console.log(err);
                    respJson(result, response);
                }
            }); 
        }
    }
}
class TDBMySql {
    constructor(){
        this.apps={
            kms:{
                host:"tjiot.hhdata.cn" ,
                port:53306,
                uid:"root",
                pwd:"hhuc1115",
                db:"kmsv10" ,
                sqlPath:"E:/app/kmsdev/dev/svr/sqls"
            },
            hhRemoteAssist:{
                host:"tjiot.hhdata.cn" ,
                port:53306,
                uid:"root",
                pwd:"hhuc1115",
                db:"hhRemoteAssist" ,
                sqlPath:"E:/app/kmsdev/dev/svr/sqls"
            }
        };
        this.pools={};
    }
    getConn  (appName , callback) {
        var me = this;
        var res ={ conn:null};
        try{
            if( me.apps[appName] ){
                var dbInfo = me.apps[appName];
                if( !dbInfo.pool){
                    dbInfo.pool = mysql.createPool({
                        host: dbInfo.host ,
                        port: dbInfo.port ,
                        user: dbInfo.uid ,
                        password: dbInfo.pwd ,
                        database: dbInfo.db,
                        connectionLimit:100,
                        multipleStatements:true
                    });
                }
                dbInfo.pool.getConnection( function (err, conn) {
                    if( conn &&conn!=null){
                        conn.config.queryFormat = function (query, values) {
                            if (!values) return query;
                            return query.replace(/\:(\w+)/g, function (txt, key) {
                                    if (values.hasOwnProperty(key)) {
                                    return this.escape(values[key]);
                                    }
                                    return txt;
                                }.bind(this));
                        };
                        
                    }
                    res.error = err;
                    res.conn = conn;
                    callback(res);
                })
            }
            else{
                res.error="appName is error."
                callback( res);
            }
        }
        catch (e) {
            console.log(e);
        }
    };
    query( appId ,  sql , ps , callback){
        var res ={ result:null};
        var me = this;
        try{
            me.getConn(appId , function (resConn) {
                if( resConn.error){
                    callback( resConn);
                }
                else {
                    var s = resConn.conn.format(sql , ps);
                    console.log(s);
                    resConn.conn.query(sql, ps, function (errQuery, results, fields) {
                        resConn.conn.release();
                        if (errQuery) {
                            var s = resConn.conn.format(sql , ps);
                            res.error = errQuery;
                            console.log(errQuery, s, ps);
                            callback( res );
                        } else {
                            res.result = JSON.parse(JSON.stringify(results));
                            callback( res);
                        }
                    })
                }
            })
        }
        catch (e) {
            console.log(e);
        }
    };
    queryByFn( appName ,  sqlPath , ps , callback){
        var res ={  result:null};
        var me = this;
        try{
            if( me.apps[appName] ){
                var absPath = me.apps[appName].sqlPath+"/"+sqlPath;
                absPath = absPath.replace("//","/");
                if( absPath.indexOf(".txt")<=0 ){
                    absPath = absPath+".txt";
                }
                fs.exists(absPath, function (flag) {
                    if( flag){
                        var sql = fs.readFileSync(absPath, 'utf-8');
                        me.query(appName, sql , ps , function (resDB) {
                            res = resDB;
                            callback( res);
                        })
                    }
                    else{
                        res.error = "sqlPath is error.";
                        callback( res);
                    }
                })
            }
            else{
                res.error="appName is error."
                callback( res);
            }
        }
        catch (e) {
            console.log(e);
        }
    };
    setConfig(dbKey , dbInfo){
        var me = this;
        try{
            me.apps[dbKey]= dbInfo;
        }
        catch(er){
            console.log(er);
        }
    };
    regist2WebSvr(webSvr){
        var me = this;
        if( webSvr && webSvr!=null && webSvr.registRoute){
            var respJson = function(result , response){
                try{
                    response.send(JSON.stringify(result));
                }
                catch(eer){
                    console.log(eer);
                }
            }
            webSvr.registRoute ( "common" , "mysqldb" , "query" , function( result , request , response ){                
                try{
                    var appId = result.ps.appId ; 
                    var key = result.ps.key ; 
                    var psJson = result.ps.psJson;
                    var ps = {};
                    try{
                        ps = JSON.parse(psJson);
                    }
                    catch(eer){
                        ps={};
                    }
                    me.queryByFn(appId , key , ps , function(res){
                        result.data = res;
                        respJson(result, response);
                    }) ;
                }
                catch(err){
                    console.log(err);
                    respJson(result, response);
                }
            }); 
        }
    }
}

module.exports = {
    TDBMs : TDBMs ,
    TDBMySql : TDBMySql
}